package com.kk.Utils;

import com.kk.entity.Homework;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelToHomeworkUtil {
    public static List<Homework> readExcel(MultipartFile file) {
        List<Homework> homeworkList = new ArrayList<>();
        int idCounter = 1; // 初始化 id 计数器
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            // 假设第一行为表头，从第二行开始读取数据
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Homework homework = new Homework();
                homework.setId(idCounter++); // 自动递增 id
                homework.setName(getCellStringValue(row.getCell(1)));
                homework.setFeishuNumber(getCellStringValue(row.getCell(2)));
                homework.setHomeworkType(getCellStringValue(row.getCell(3)));
                homework.setHomeworkLink(getCellStringValue(row.getCell(4)));
                homework.setHomeworkAttachment(getCellStringValue(row.getCell(5)));
                homework.setSubmissionDate(getCellDateValue(row.getCell(6)));
                homework.setScore(getCellDoubleValue(row.getCell(7)));
                homework.setGrade(getCellStringValue(row.getCell(8)));
                homework.setRemarks(getCellStringValue(row.getCell(9)));
                homework.setCorrector(getCellStringValue(row.getCell(10)));
                homework.setSeatNumber(getCellStringValue(row.getCell(11)));

                homeworkList.add(homework);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return homeworkList;
    }

    private static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        return cell.getStringCellValue();
    }

    private static Double getCellDoubleValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        } else if (cell.getCellType() == CellType.STRING) {
            try {
                return Double.parseDouble(cell.getStringCellValue());
            } catch (NumberFormatException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private static java.util.Date getCellDateValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else if (cell.getCellType() == CellType.STRING) {
            try {
                return new SimpleDateFormat("yyyy-MM-dd HH:mm").parse(cell.getStringCellValue());
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}